#!/usr/bin/python27
#coding=utf8
from openpyxl import load_workbook
import sys,csv
reload(sys)
sys.setdefaultencoding('utf-8')

def gen_data(fname):
    from cvm.common.RunSql import RunSQl
    sql_list = []
    print 'start.......'
    # 打开excel数据文件
    csvReader = csv.reader(open(fname, 'rb'))  # 以只读方式取得csv文件中内容
    rownum = 0
    for row in csvReader:  # 行循环
        if rownum > 500:
            break
        rownum = rownum + 1
        print 'Row No.:', rownum
        parameterStr = ','.join(row)  # 通过逗号连接每行每个单元格的内容
        a = parameterStr.replace('null', '')
        parameters = a.split(',')  # 得到每个单元格的内容
        datamonth = parameters[0].split('-')
        year = datamonth[0]
        month = datamonth[1]
        day = datamonth[2].split(' ')[0]
        province = parameters[1]
        city = parameters[2]
        town = parameters[3]
        brand = parameters[4]
        vehicle_model = parameters[5]
        vehicle_series = parameters[6]
        vehicle_name = parameters[7]
        vehicle_body = parameters[8]
        color = parameters[9]
        displacement = parameters[10]
        domestic = parameters[11]
        country = parameters[12]
        manufacturer = parameters[13]
        birthyear = parameters[14]
        gender = parameters[15]
        usage = parameters[16]
        ownership = parameters[17]
        paytype = parameters[18]
        number = parameters[19]
        sql = """
        insert into cvm_cyc (year,month,day,province,city,town,enterpricegroup,foreigngroup,manufacturer,brand,
        vehicle_series,vehicle_model,vehicle_name,level,vehicle_classification,Vehicle_subdivision,vehicle_body,
        fuel_type,color,displacement,domestic,birthyear,gender,usefor,ownership,paytype,number,create_time,
        update_time
        ) values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',
        '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',%d,now(),now())
        """ % (
            year,month,day,province.replace('"', ''),city,town,'','',manufacturer,brand,vehicle_series,vehicle_model,vehicle_name,'',
            '','',vehicle_body,'',color,displacement,domestic,birthyear,gender,usage,ownership,paytype,int(number)
        )
        sql_list.append(sql)
    runsql = RunSQl()
    runsql.create_sqllist_file(sql_list,filename='D:\cvm_cyc.sql')
    print 'over.......'


def gen_data_from_excel(fname):
    from cvm.common.RunSql import RunSQl
    sql_list = []
    print 'start.......'
    wb = load_workbook(filename=fname)
    sheet_ranges = wb['sheet1']
    # 循环读取2到65534行
    for rownum in range(2, 500):
        print 'Row No.:', rownum
        datamonth = str(sheet_ranges.cell(row=rownum, column=1).value)
        year = datamonth[0:4]
        month = datamonth[4:]
        day = ''
        province = str(sheet_ranges.cell(row=rownum, column=2).value)
        city = str(sheet_ranges.cell(row=rownum, column=3).value)
        town = ''
        enterpricegroup = str(sheet_ranges.cell(row=rownum, column=4).value)
        foreigngroup = str(sheet_ranges.cell(row=rownum, column=5).value)
        manufacturer = str(sheet_ranges.cell(row=rownum, column=6).value)
        brand = str(sheet_ranges.cell(row=rownum, column=7).value)
        vehicle_series = str(sheet_ranges.cell(row=rownum, column=8).value)
        vehicle_model = str(sheet_ranges.cell(row=rownum, column=9).value)
        vehicle_name = ''
        level = str(sheet_ranges.cell(row=rownum, column=10).value)
        vehicle_classification = str(sheet_ranges.cell(row=rownum, column=11).value)
        vehicle_subdivision = str(sheet_ranges.cell(row=rownum, column=12).value)
        vehicle_body = str(sheet_ranges.cell(row=rownum, column=13).value)
        fuel_type = str(sheet_ranges.cell(row=rownum, column=14).value)
        displacement = str(sheet_ranges.cell(row=rownum, column=15).value)
        domestic = str(sheet_ranges.cell(row=rownum, column=16).value)
        color = str(sheet_ranges.cell(row=rownum, column=17).value)
        usage = str(sheet_ranges.cell(row=rownum, column=18).value)
        ownership = str(sheet_ranges.cell(row=rownum, column=19).value)
        number = str(sheet_ranges.cell(row=rownum, column=20).value)
        sql = """
                insert into cvm_cyc (year,month,day,province,city,town,enterpricegroup,foreigngroup,manufacturer,brand,
                vehicle_series,vehicle_model,vehicle_name,level,vehicle_classification,Vehicle_subdivision,vehicle_body,
                fuel_type,color,displacement,domestic,birthyear,gender,usefor,ownership,paytype,number,create_time,
                update_time
                ) values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',
                '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',%d,now(),now())
                """ % (
            year, month, day, province.replace('"', ''), city, town, enterpricegroup, foreigngroup, manufacturer, brand, vehicle_series,
            vehicle_model, vehicle_name, level,
            vehicle_classification, vehicle_subdivision, vehicle_body, fuel_type, color, displacement, domestic, '', '', usage, ownership, '',
            int(number)
        )
        sql_list.append(sql)
    runsql = RunSQl()
    runsql.create_sqllist_file(sql_list,filename='D:\cvm_cyc.sql')
    print 'over.......'

if __name__ == '__main__':
    pass
    # gen_data('E:\capitalvue\cvm\gccyc_201508_500.csv')
    # gen_data('E:\capitalvue\cvm\jkcyc_201508_500.csv')
    gen_data_from_excel(u'E:\capitalvue\cvm\山东车险201611.xlsx')